/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package occho.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import occho.model.Asignatura;
import occho.model.Pregunta;
import occho.model.Test;
import occho.model.Usuario;

/**
 *
 * @author Jorge
 */
public class OracleTestDAO implements TestDAO {
    
    private Connection conn = null;

    /**
     * Constructor de la clase
     * @param conn
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public OracleTestDAO(Connection conn) throws ClassNotFoundException, SQLException {
        this.conn = conn;
    }

    @Override
    public boolean insertarTest(Test test, Asignatura asig) throws SQLException {
        int nextId;
        //select seq_test.nextval from dual;
        PreparedStatement stmt = conn.prepareStatement("select seq_test.nextval from dual");
        stmt.clearParameters();
        ResultSet rs = stmt.executeQuery();
        rs.next();
        nextId= rs.getInt("nextval");
        test.setIdTest(nextId);
        
        //inserta en la tabla test
        stmt = conn.prepareStatement(
                "insert into Test (idtest, idAsignatura, fechaInicio, fechaFin, numPreguntas, nombre,descripcion) values (?,?,?,?,?,?,?)");
        stmt.clearParameters();
        stmt.setInt(1, nextId);
        stmt.setInt(2, asig.getIdAsignatura());
        stmt.setTimestamp(3, new Timestamp(test.getFechaInicio().getTime()));
        stmt.setTimestamp(4, new Timestamp(test.getFechaFin().getTime()));
        stmt.setInt(5, test.getNumPreguntas());
        stmt.setString(6, test.getNombre());
        stmt.setString(7, test.getDescripcion());
        if (stmt.executeUpdate() > 0) {
            stmt.close();
            return true;
        }
        else {  
            stmt.close();
            return false;
        }
    }

    @Override
    public boolean insertarTestPreguntas(Test test) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(
                "insert into Test_Pregunta (idtest, idPregunta) values (?,?)");
            for (Pregunta p : test.getPreguntas()){
                stmt.clearParameters();
                stmt.setInt(1, test.getIdTest());
                stmt.setInt(2, p.getIdPregunta());
                if (stmt.executeUpdate() < 0) {
                    return false;
                }
            }
            stmt.close();
            return true;
    }
    
    @Override
    public boolean borrarTest(Test test) throws SQLException {
        borrarTestPregunta(test);
        PreparedStatement stmt = conn.prepareStatement("delete from Test_v where idTest = ?");
        stmt.clearParameters();
        stmt.setInt(1, test.getIdTest());
        if (stmt.executeUpdate() > 0) {
            stmt.close();
            return true;
        }
        else {
            stmt.close();
            return false;
        }
    }
    
    @Override
    public boolean borrarTestPregunta(Test test) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement("delete from Test_Pregunta_v where idTest = ?");
        stmt.clearParameters();
        stmt.setInt(1, test.getIdTest());
        if (stmt.executeUpdate() > 0) {
            stmt.close();
            return true;
        }
        else {
            stmt.close();
            return false;
        }
    }

    @Override
    public Test seleccionarTest(int idTest) throws SQLException {
        Test t = null;
        PreparedStatement stmt = conn.prepareStatement("select * from test where idtest = ?");
        stmt.clearParameters();
        stmt.setInt(1, idTest);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            t = new Test(rs.getInt("idtest"),rs.getString("nombre"),rs.getString("descripcion"),rs.getTimestamp("fechainicio"),rs.getTimestamp("fechafin"));
        }
        rs.close();
        stmt.close();
        return t;
    }

    @Override
    public List<Test> seleccionarTests() throws SQLException {
        ArrayList<Test> lista = new ArrayList<Test>();
        PreparedStatement stmt = conn.prepareStatement("select * from test");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            lista.add(new Test(rs.getInt("idtest"),rs.getString("nombre"),rs.getString("descripcion"),rs.getTimestamp("fechainicio"),rs.getTimestamp("fechafin")));
        }
        rs.close();
        stmt.close();
        return lista;
    }

    @Override
    public List<Test> seleccionarTests(Asignatura asig) throws SQLException, ClassNotFoundException {
        ArrayList<Test> lista = new ArrayList<Test>();
        PreparedStatement stmt = conn.prepareStatement("select * from test where idasignatura = ?");
        stmt.clearParameters();
        stmt.setInt(1, asig.getIdAsignatura());
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Test t = new Test(rs.getInt("idtest"),rs.getString("nombre"),
                        rs.getString("descripcion"),rs.getTimestamp("fechainicio"),rs.getTimestamp("fechafin"));
            t.setPreguntas(seleccionarTestPreguntas(t));
            lista.add(t);
        }
        rs.close();
        stmt.close();
        return lista;
    }
    

    @Override
    public List<Test> seleccionarTests(Usuario usuario, Asignatura asignatura) throws SQLException,ClassNotFoundException {
        ArrayList<Test> lista = new ArrayList<Test>();
        PreparedStatement stmt = conn.prepareStatement(
                "select distinct t.idtest from pregunta_alumno pa, test t " +
                "where t.idtest = pa.idtest and " +
                "pa.idalumno = ? and " +
                "t.idasignatura = ?");
        stmt.clearParameters();
        stmt.setInt(1, usuario.getIdUsuario());
        stmt.setInt(2, asignatura.getIdAsignatura());
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Test t = seleccionarTest(rs.getInt("idtest"));
            t.setPreguntas(seleccionarPreguntasRespondidas(rs.getInt("idtest"), usuario.getIdUsuario()));
            lista.add(t);
        }
        rs.close();
        stmt.close();
        
        return lista;
    }

    @Override  
    public Set<Pregunta> seleccionarTestPreguntas(Test t) throws SQLException, ClassNotFoundException {
        HashSet<Pregunta> pregs = new HashSet<Pregunta>();
        OracleDAOFactory daoFactory = OracleDAOFactory.getInstancia();
        PreguntaDAO preguntaDAO = daoFactory.getPreguntaDAO();
        PreparedStatement stmt = conn.prepareStatement("select * from test_pregunta where idtest = ?");
        stmt.clearParameters();
        stmt.setInt(1, t.getIdTest());
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            pregs.add(preguntaDAO.seleccionarPregunta(rs.getInt("idpregunta")));
        }
        rs.close();
        stmt.close();
        return pregs;
    }
    
    @Override
    public Set<Pregunta> seleccionarPreguntasRespondidas(int idTest, int idAlumno) throws SQLException, ClassNotFoundException {
        HashSet<Pregunta> pregs = new HashSet<Pregunta>();
        OracleDAOFactory daoFactory = OracleDAOFactory.getInstancia();
        PreguntaDAO preguntaDAO = daoFactory.getPreguntaDAO();
        String sql = "select p.idpregunta, pa.acertada " + 
                "from pregunta_alumno pa, pregunta p " +
                "where pa.idpregunta = p.idpregunta and pa.idalumno = ? and pa.idtest = ?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.clearParameters();
        stmt.setInt(1, idAlumno);
        stmt.setInt(2, idTest);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Pregunta p = preguntaDAO.seleccionarPregunta(rs.getInt("idpregunta"));
            p.setAcertada(rs.getBoolean("acertada"));
            pregs.add(p);
        }
        rs.close();
        stmt.close();
        return pregs;
    }

   

}
